<?php
    class StudentDormitoryExchangingDAL{
        //获取学生列表
        public function getStudentList($student_id,$grade,$major,$class,$search,$page,$pageSize){
            $query_page='select FOUND_ROWS() as count;';
            $query="select SQL_CALC_FOUND_ROWS grade,tb_major_list.major_id,tb_major_list.major_name,
            tb_student_info_list.name,tb_student_info_list.gender,tb_student_info_list.student_id,tb_student_info_list.class,
            tb_apartment_info.apartment_id,tb_apartment_info.apartment_name,tb_student_dormitory_roster.floor,tb_student_dormitory_roster.dormitory_id 
            from tb_student_dormitory_roster,tb_student_info_list,tb_major_list,tb_apartment_info 
            where tb_student_dormitory_roster.student_id=tb_student_info_list.student_id 
            and tb_student_dormitory_roster.status=1 
            and tb_student_info_list.major_id=tb_major_list.major_id 
            and tb_student_dormitory_roster.apartment_id=tb_apartment_info.apartment_id 
            and tb_student_dormitory_roster.student_id!=:student_id 
            and tb_student_info_list.grade=:grade 
            and tb_student_info_list.major_id=:major_id 
            and tb_student_info_list.class=:class ".
            ($search==''?'':'and (tb_student_info_list.student_id=:search_id or tb_student_info_list.name=:search_name) ')."
            order by tb_student_info_list.student_id LIMIT :page,:pageSize;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(':student_id',$student_id);
            $statement->bindValue(':grade',$grade);
            $statement->bindValue(':major_id',$major);
            $statement->bindValue(':class',$class);
            if(!$search==''){
                $statement->bindValue(':search_id',$search);
                $statement->bindValue(':search_name',$search);
            }
            $statement->bindValue(":page",$page,PDO::PARAM_INT);
            $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);

            if($statement->execute()){
                $studentList=$statement->fetchALL(PDO::FETCH_ASSOC);
                $row=$pdo->query($query_page);
                $count=$row->fetch(PDO::FETCH_ASSOC);
                $row->closeCursor();

                $result=array(
                    'studentList'=>$studentList,
                    'count'=>$count['count']
                );
            }

            return $result;
        }

        public function exchangeStudentDormitory($exchangeInfo){
            $query_exchange="update tb_student_dormitory_roster set 
            apartment_id=:apartment_id,floor=:floor,dormitory_id=:dormitory_id 
            where student_id=:student_id;";
            $pdo=myPDO::getInstance();
            try{
                $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
                $pdo->beginTransaction();

                $statement=$pdo->prepare($query_exchange,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(':apartment_id',$exchangeInfo['apartment_id']);
                $statement->bindValue(':floor',$exchangeInfo['floor']);
                $statement->bindValue(':dormitory_id',$exchangeInfo['dormitory_id']);
                $statement->bindValue(':student_id',$exchangeInfo['exchange_student_id']);
                $statement->execute();

                $statement->bindValue(':apartment_id',$exchangeInfo['exchange_apartment_id']);
                $statement->bindValue(':floor',$exchangeInfo['exchange_floor']);
                $statement->bindValue(':dormitory_id',$exchangeInfo['exchange_dormitory_id']);
                $statement->bindValue(':student_id',$exchangeInfo['student_id']);
                $result=$statement->execute();

                $pdo->commit();  
            }catch(Exception $e){
                $pdo->rollBack();
                //echo "Failed: ".$e->getMessage();
            }
            $statement->closeCursor();

            return $result;
        }
    }
?>